﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using System.Data.Sql;
using System.Data.SqlClient;

namespace DBWorker
{
    /// <summary>
    /// Class working with MSSQl Database
    /// </summary>
    public class DBWorkerMSSQL : DBWokerBase//, IDBWorker
    {
        #region Consctuctors and Fields
        static private string _dbPath = null;
        static private string _dbSqlDataSource = ".\\SQLEXPRESS";
        private static MSSQLType _dbType;

        public DBWorkerMSSQL()
            : base()                                                
        {
        }
        public DBWorkerMSSQL(string server, string databaseName)
            : base(server, databaseName)                            
        {
            Path = server + "\\" + databaseName;
        }

        public DBWorkerMSSQL(string path)
            : base(path)                                            
        {
            Path = path;
        }
        #endregion

        #region Properties
        /// <summary>
        /// Path to database file. 
        /// </summary>
        public string Path                                          
        {
            set
            {
                _dbPath = value;
            }
            get
            {
                return _dbPath;
            }
        }
        /// <summary>
        /// Type of MS database installed server
        /// </summary>
        public string DataSource                                    
        {
            set{ _dbSqlDataSource = value; }
            get{ return _dbSqlDataSource;  }
        }
        /// <summary>
        /// Database type property.
        /// </summary>
        public MSSQLType Type                                       
        {
            set
            {
                _dbType = value;
            }
            get
            {
                return _dbType;
            }
        }
        /// <summary>
        /// Forms connection string depending on MSSQL server type
        /// </summary>
        /// <returns>Connection string</returns>
        public override string GetConnectionString()                
        {
            switch(Type)
            {
                case MSSQLType.Local:
                    {
                        if (Path != null)
                            return "Data Source=" + _dbSqlDataSource + ";AttachDbFilename=" + _dbPath + ";Integrated Security=True;Connect Timeout=30;User Instance=True";
                        else
                            throw new Exception("Error forming connection string");
                    };break;
                case MSSQLType.RemoteSQLAuth:
                    {
                        string conn = "";
                        conn = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}",
                            _dbSqlDataSource, base.DatabaseName, base.UserID, base.Password);
                        return conn;
                    }
                case MSSQLType.RemoteWinAuth:
                    {
                        string conn = null;
                        conn = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=True",
                            _dbSqlDataSource, base.DatabaseName);
                        return conn;
                    }
            }
            return "";            
        }
        #endregion

        #region Public Methods
        public DataTable GetDataTableByCommand(string query)            
        {
            SqlCommand sqlCommQuery = new SqlCommand(@query);
            return GetDataTableByCommand(sqlCommQuery);
        }

        public DataTable GetDataTableByCommand(SqlCommand command)      
        {
            SqlConnection sqlConn = new SqlConnection( GetConnectionString() );
            command.Connection = sqlConn;

            return base.GetDataTableByCommand(command);
        }

        public DataTable GetDataTableByTableName(string tableName)      
        {
            return this.GetDataTableByCommand("SELECT * FROM " + @tableName);
        }

        public DataRow GetDataRowByCommand(string query)                
        {
            SqlCommand sqlCommQuery = new SqlCommand(@query);
            return GetDataRowByCommand(sqlCommQuery);
        }

        public DataRow GetDataRowByCommand(SqlCommand command)          
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.GetDataRowByCommand(command);
        }

        public object GetObjectByCommand(string query)                  
        {
            SqlCommand sqlCommQuery = new SqlCommand(@query);
            return GetObjectByCommand(sqlCommQuery);
        }

        public object GetObjectByCommand(SqlCommand command)            
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.GetObjectByCommand(command);
        }

        public DataTable GetDataTableSchema(string query)               
        {
            SqlCommand sqlCommQuery = new SqlCommand(@query);
            return GetDataTableSchema(sqlCommQuery);
        }

        public DataTable GetDataTableSchema(SqlCommand command)         
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.GetDataTableSchema(command);
        }

        public void ExecuteScalar(string query)                         
        {
            SqlCommand sqlCommQuery = new SqlCommand(@query);
            ExecuteScalar(sqlCommQuery);
        }

        public object ExecuteScalar(SqlCommand command)                   
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.ExecuteScalar(command);
        }

        public object ExecuteScalarLastIdentity(SqlCommand command)
        {
            string added = "; SELECT SCOPE_IDENTITY();";
            command.CommandText = command.CommandText + added;

            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.ExecuteScalar(command);
        }

        /// <summary>
        /// Create new database
        /// </summary>
        /// <param name="dbName">Database name</param>
        /// <param name="dbPath">Path to database file(mdf)</param>
        public void CreateDataBase(string dbName, string dbPath)        
        {
            String str;
            SqlConnection myConn = new SqlConnection("Server=" + DataSource + ";Integrated security=SSPI;database=master");

            str = String.Format(
                    "CREATE DATABASE {0} ON PRIMARY " +
                    "(NAME = {0}, " +
                    "FILENAME = '{1}', " +
                    "SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) ",
                    dbName, dbPath);

            // Drop database if exists
            SqlCommand CommandDrop = new SqlCommand(
                "IF EXISTS(SELECT name FROM sys.databases " +
                " WHERE name = '" + dbName + "') " +
                " DROP DATABASE " + dbName, myConn);
            try
            {
                myConn.Open();
                CommandDrop.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                //throw new Exception(ex.ToString());
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

            // Creating new database
            SqlCommand CommandCreate = new SqlCommand(str, myConn);
            try
            {
                myConn.Open();
                CommandCreate.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }
        #endregion
    }

    /// <summary>
    /// MSSQL database type
    /// </summary>
    public enum MSSQLType
    {
        /// <summary>
        /// Local DB (access through files)
        /// </summary>
        Local,
        /// <summary>
        /// Remote server access, SQL authentication
        /// </summary>
        RemoteSQLAuth,
        /// <summary>
        /// Remote server access, Windows authentication
        /// </summary>
        RemoteWinAuth
    }
}
